package com.pfs.internal.timestamp.reports;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;


public class ReportUtil {
	public static Font createFont(Workbook wb, short bold, short height, boolean isItalic) {
		Font font = wb.createFont();
		font.setBoldweight(bold);
		font.setFontHeightInPoints((short) height);
		if (isItalic) font.setItalic(true);
		font.setFontName("Times New Roman");
		return font;
	}

	public static CellStyle createCellStyle(Workbook wb, short alignment,Font font, short format, boolean wraptext, short border) {
		CellStyle cs = wb.createCellStyle();
		cs.setAlignment(alignment);
		cs.setFont(font);
		cs.setVerticalAlignment(CellStyle.VERTICAL_TOP);
		if (wraptext) cs.setWrapText(true);
		if (format != 0) cs.setDataFormat(format);
		cs.setBorderBottom(border);
		cs.setBorderLeft(border);
		cs.setBorderRight(border);
		cs.setBorderTop(border);
		return cs;
	}
		
	public static void createCell(Row row, short col, CellStyle cs,	String content) {
		Cell cell = row.createCell(col);
		cell.setCellType(Cell.CELL_TYPE_STRING);
		cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		
		cs.setWrapText(true);
		if (cs != null)	cell.setCellStyle(cs);
		cell.setCellValue(new HSSFRichTextString(content));
	}

	public static  void createCell(Row row, short col, CellStyle cs,	double content) {
		Cell cell = row.createCell(col);
		cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		if (cs != null)	cell.setCellStyle(cs);
		cell.setCellValue(content);
	}
	
	public static void createRegion(Sheet sheet, Row row, short rf, short rt, short cf, short ct, CellStyle cs, String content ) {
		Cell cell = row.createCell((short) cf);
	    cell.setCellValue(content);
	    if (cs!=null) cell.setCellStyle(cs);
		sheet.addMergedRegion(new CellRangeAddress(rf, rt, cf, ct));
	}
	
	public static void createRegion(Sheet sheet, Row row, RegionStyle regionStyle, ResultSet rs, int i, int rowNum) throws SQLException {
		Cell cell = row.createCell((short)regionStyle.getCol_from());
		cell.setCellStyle(regionStyle.getCell_style());
		for (short j = regionStyle.getCol_from(); j <= regionStyle.getCol_to(); ++j) {
		    Cell cell_temp = row.createCell(j);
		    cell_temp.setCellStyle(regionStyle.getCell_style());
		    if(j==regionStyle.getCol_from()) {
		    	setCellValue(cell, regionStyle, rs, i);
		    }
		}
		sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, regionStyle.getCol_from(), regionStyle.getCol_to()));
	}
	
	public static void setCellValue(Cell cell, RegionStyle regionStyle, ResultSet rs, int i) throws SQLException {
		switch(regionStyle.getCELL_TYPE()) {
			case Cell.CELL_TYPE_STRING:
				cell.setCellValue(rs.getString(i));
				cell.setCellType(Cell.CELL_TYPE_STRING);
				break;
			case Cell.CELL_TYPE_NUMERIC:
				cell.setCellValue(rs.getDouble(i));
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				break;
			default: break;
		}
		
	}
	
	public static byte[] wb2zip(Workbook wb,String file_name) throws Exception 
	{
		byte[] bytes = null;
		try 
		{
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ZipOutputStream zos = new ZipOutputStream(baos);
			zos.setLevel(9);
			ZipEntry ze = new ZipEntry(file_name+".xls");
			zos.putNextEntry(ze);
			wb.write(zos);
			zos.closeEntry();
			zos.close();
			bytes = baos.toByteArray();
			baos.close();
		}
		catch (Exception ex) 
		{
			throw ex;
		}
		return bytes;
	}
	
	public static byte[] zipBytes(byte[] input, String filename) throws IOException {
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ZipOutputStream zos = new ZipOutputStream(baos);
		ZipEntry entry = new ZipEntry(filename);
		entry.setSize(input.length);
		zos.putNextEntry(entry);
		zos.write(input);
		zos.closeEntry();
		zos.close();
		return baos.toByteArray();
	}
	
	public static void auto_size_column(Workbook wb,int columns) throws Exception {
		Sheet sheet;
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			sheet = wb.getSheetAt(i);
			for (int j = 0; j < columns; j++) 
				sheet.autoSizeColumn(j);
		}
	}
}
